Re: index item size 4496 exceeds maximum 2713

Hello all,

Thank you all very much for the input. I appreciate it. It turns out that
indeed, the biggest thing that can be indexed using btree is some division
(by 3 or 4, it seems to differ) of the size of the tuple size. (Thanks Tom
Lane and Andrew McMillan for pointing me in that direction)

I ended up changing the backend to a 32768 size block, (by using
BLCKSZ=32678 in the pg_configure.h.in file, then running:
configure/gmake/gmake install) which gave me the 8192 (32,768/4) that I had
been looking for.

Be careful if you do this! Make a pg_dump of your current database first!

For those interested, (and since how to do this doesn't seem to exist
anywhere, or it is VERY hard to find anyway) here is how I did it:

Be careful. please.

1. pd_dumpall > outputfile      / This makes a backup of your current db.
2. pg_ctl stop -D /usr/local/pgsql/data     /stop the pg process
3. Change the file /usr/local/src/postgresql-X.X/src/include/configure.h.in
to reflect the new blocksize that you desire. BLCKSZ = 32768 The 32k number
is the max that you can use, and I would stick with either 8192 or 16384 or
32768.
4. Re-run the configure, then the gmake, then the gmake install.
5. Move the /usr/local/pgsql/data directory or rename it. Try:
 mv /usr/local/pgsql/data /usr/local/pgsql/data.old or something like that.
When this is over, you can delete this, but in case you need to go back,
hang on to it. It is your old database(s).
6. Make a new data dir.
(root)# mkdir /usr/local/pgsql/data
(root)# chown postgres /usr/local/pgsql/data
(root)# initlocation -D /usr/local/pgsql/data
(root)# initdb -D /usr/local/pgsql/data
(root)# su postgres
(postgres)# postmaster -D /usr/local/pgsql/data
(postgres)# /usr/local/pgsql/bin/psql -d template1 -f outputfile
(where outputfile is the one you created in step 1.)

That's it. You now have a 32k block size.

btw, I will be trying to implememnt tsearch on this db too. I want it
blazing fast!! faster! Faster! FASTER!!

Jon Hassen
Systems Manager
Arizona Daily Star